preserve

drop if filer == 0
drop if nonRecon == 1

keep if flpdyr == 2019 | flpdyr == 2021
* Want single filers with one child for Figure 1
keep if (mars == 1) | (mars == 4)
keep if famSize == 2

* In 2021 sample, note that many households with income > 400% FPL have a PTC, but not in 2019. This is due to ARPA PTC for UI policy
capture drop xtile*
capture drop temp*
gen temp = exch_income
replace temp = 0 if temp < 0
sum temp if temp >= 70000 [w=wt]
replace temp = r(mean) if temp >= 70000
bysort flpdyr: gen xtile = floor(temp / 10000)

* In the top xtile, there is a single observation generating a PTC > 0. This family is clearly not actually eligible but claims $11,496
* 	For the purposes of this graph we assume this is denied/clawed back and zero it out
replace PTC = 0 if PTC > 0 & xtile > 7

collapse (mean) APTC PTC temp [w = wt], by(flpdyr xtile) fast
rename temp exch_income
reshape wide APTC PTC exch_income, i(xtile) j(flpdyr)

gen agi_bin = ""
local i = 0
foreach bin in "<10" "10-20" "20-30" "30-40" "40-50" "50-60" "60-70" "70+" {
	replace agi_bin = "`bin'" if xtile == `i'
	local i = `i' + 1
}

local day = day(date(c(current_date), "DMY"))
if(`day' < 10) local day = ".0" + "`day'"
else local day = "." + "`day'"
local month = month(date(c(current_date), "DMY"))
if(`month' < 10) local month = ".0"+"`month'"
else local month = "." + "`month'"
local year = year(date(c(current_date), "DMY"))

capture mkdir "$workingDir/output/Figure_1/old"
capture copy "$workingDir/output/Figure_1/Figure_1_PTC_by_HHAGI.xlsx" "$workingDir/output/Figure_1/old/`year'`month'`day'_Figure_1_PTC_by_HHAGI.xlsx"

order agi_bin PTC2019 APTC2019 PTC2021 APTC2021 exch_income2019
sort xtile
keep PTC2019 APTC2019 PTC2021 APTC2021 exch_income2019

mkmat PTC2019 APTC2019 PTC2021 APTC2021 exch_income2019, matrix(output)
putexcel set "$workingDir/output/Figure_1/Figure_1_PTC_by_HHAGI.xlsx", sheet("V1_data") modify
putexcel B1 = matrix(output), colnames
putexcel close

restore


* This second graph is a "synthetic" schedule, allowing income to change but holding other PTC calculation inputs constant by averaging
* 	within bin
preserve

drop if filer == 0
keep if flpdyr == 2019
* Want just single files with a single child for Figure 1
keep if (mars == 1) | (mars == 4)
keep if famSize == 2

foreach var in APTC SLCSP Prem {
	sum `var' [w = wt]
	scalar s`var' = r(mean)
}

clear
insobs 8
gen xtile = _n
gen flpdyr = 2019
gen famSize = 2
gen stateFPL = 1
gen mars = 4

gen APTC = sAPTC
gen prem = sPrem
gen SLCSP = sSLCSP

gen exch_income = .
local tempinc = 5000
forvalues i = 1/8 {
	replace exch_income = `tempinc' if xtile == `i'
	local tempinc = `tempinc' + 10000
}

* Want prior year's FPL levels (see MY17 run 1420_17, exchange_create ~line 122)
replace flpdyr = flpdyr - 1
merge m:1 flpdyr stateFPL famSize using "$dataDir/fpl.dta"
replace flpdyr = flpdyr + 1
keep if _merge == 3

capture drop _merge
merge m:1 flpdyr using "$dataDir/af.dta"
keep if _merge == 3
drop _merge

gen calcFPL = round(exch_income / FPL, 0.01)
replace calcFPL = 0 if calcFPL < 0

gen payout_max = 1.0
replace payout_max = AFLT133 if calcFPL < 1.33 & flpdyr < 2021
replace payout_max = AF133 + (calcFPL-1.33) * ((AF133 - AFLT133)/0.17) if calcFPL >= 1.33 & calcFPL < 1.50 & flpdyr < 2021
replace payout_max = AF150 + (calcFPL-1.5) * ((AF150 - AF133)/0.5) if calcFPL >= 1.50 & calcFPL < 2.00 & flpdyr < 2021
replace payout_max = AF200 + (calcFPL-2.0) * ((AF200 - AF150)/0.5) if calcFPL >= 2.00 & calcFPL < 2.50 & flpdyr < 2021
replace payout_max = AF250 + (calcFPL-2.5) * ((AF250 - AF200)/0.5) if calcFPL >= 2.50 & calcFPL < 3.00 & flpdyr < 2021
replace payout_max = AF300 if calcFPL >= 3.00 & calcFPL <= 4.00 & flpdyr < 2021

gen repay_gr = 1
replace repay_gr = 233.77133/230.55217 if flpdyr==2015
replace repay_gr = 236.69933/230.55217 if flpdyr==2016
replace repay_gr = 237.65125/230.55217 if flpdyr==2017
replace repay_gr = (237.65125/230.55217)*(138.23675/135.99300) if flpdyr==2018
replace repay_gr = (237.65125/230.55217)*(140.99192/135.99300)  if flpdyr==2019
replace repay_gr = (237.65125/230.55217)*(143.32783/135.99300)  if flpdyr==2020
replace repay_gr = (237.65125/230.55217)*(144.814083/135.99300)  if flpdyr==2021

gen calc_repayLim = .
replace calc_repayLim = 300 if round(calcFPL)<200 & mars==1 
replace calc_repayLim = 750 if round(calcFPL)>=200 & round(calcFPL)<300 & mars==1 
replace calc_repayLim = 1275 if round(calcFPL)>=300 & round(calcFPL)<400 & mars==1 
replace calc_repayLim = 600 if round(calcFPL)<200 & mars!=1
replace calc_repayLim = 1500 if round(calcFPL)>=200 & round(calcFPL)<300 & mars!=1
replace calc_repayLim = 2550 if round(calcFPL)>=300 & round(calcFPL)<400 & mars!=1
replace calc_repayLim = floor(calc_repayLim * repay_gr * 0.02) * 50

gen maxOOPPrem = payout_max * exch_income if exch_income > 0
gen PTC = min(prem, max(SLCSP - maxOOPPrem, 0))
rename PTC PTC2019
keep flpdyr xtile PTC2019

local day = day(date(c(current_date), "DMY"))
if(`day' < 10) local day = ".0" + "`day'"
else local day = "." + "`day'"
local month = month(date(c(current_date), "DMY"))
if(`month' < 10) local month = ".0"+"`month'"
else local month = "." + "`month'"
local year = year(date(c(current_date), "DMY"))

capture mkdir "$workingDir/output/Figure_1/old"
capture copy "$workingDir/output/Figure_1/Figure_1_PTC_by_HHAGI.xlsx" "$workingDir/output/Figure_1/old/`year'`month'`day'_Figure_1_PTC_by_HHAGI.xlsx"

order flpdyr xtile PTC2019
sort xtile
keep flpdyr xtile PTC2019

mkmat flpdyr xtile PTC2019, matrix(output)
putexcel set "$workingDir/output/Figure_1/Figure_1_PTC_by_HHAGI.xlsx", sheet("V2_synth") modify
putexcel A1 = matrix(output), colnames
putexcel close

restore


* This third graph is identical to the first, but graphs the schedule for 2021 instead of 2019
preserve

* Figure 1 uses PTC, so drop non-filers/reconcilers so APTC and PTC are comparable (they could show up for APTC-only)
drop if filer == 0
drop if nonRecon == 1
keep if flpdyr == 2021

* Want just single files with a single child for Figure 1
keep if (mars == 1) | (mars == 4)
keep if famSize == 2

* In 2021 sample, note that many households with income > 400% FPL have a PTC, but not in 2019. This is likely due to ARPA PTC for UI policy
capture drop xtile*
capture drop temp*
gen temp = exch_income
replace temp = 0 if temp < 0

sum temp if temp >= 70000 [w=wt]
replace temp = r(mean) if temp >= 70000
bysort flpdyr: gen xtile = floor(temp / 10000)

collapse (mean) APTC PTC temp [w = wt], by(flpdyr xtile) fast
rename temp exch_income
reshape wide APTC PTC exch_income, i(xtile) j(flpdyr)

gen agi_bin = ""
local i = 0
foreach bin in "<10" "10-20" "20-30" "30-40" "40-50" "50-60" "60-70" "70+" {
	replace agi_bin = "`bin'" if xtile == `i'
	local i = `i' + 1
}
replace agi_bin = "70+" if xtile > 6

local day = day(date(c(current_date), "DMY"))
if(`day' < 10) local day = ".0" + "`day'"
else local day = "." + "`day'"
local month = month(date(c(current_date), "DMY"))
if(`month' < 10) local month = ".0"+"`month'"
else local month = "." + "`month'"
local year = year(date(c(current_date), "DMY"))

order agi_bin PTC2021 APTC2021 exch_income2021
sort xtile
keep PTC2021 APTC2021 exch_income2021

mkmat PTC2021 APTC2021 exch_income2021, matrix(output)
putexcel set "$workingDir/output/Figure_1/Figure_1_PTC_by_HHAGI.xlsx", sheet("V3_data_21") modify
putexcel B1 = matrix(output), colnames
putexcel close

restore


* This fourth graph is identical to the third, but increases the number of income bins to account for enhanced credits
preserve

* Figure 1 uses PTC, so drop non-filers/reconcilers so APTC and PTC are comparable (they could show up for APTC-only)
drop if filer == 0
drop if nonRecon == 1
keep if flpdyr == 2021

* Want just single files with a single child for Figure 1
keep if (mars == 1) | (mars == 4)
keep if famSize == 2

* In 2021 sample, note that many households with income > 400% FPL have a PTC, but not in 2019. This is due to ARPA PTC for UI policy
capture drop xtile*
capture drop temp*
gen temp = exch_income
replace temp = 0 if temp < 0

sum temp if temp >= 100000 [w=wt]
replace temp = r(mean) if temp >= 100000
bysort flpdyr: gen xtile = floor(temp / 10000)

collapse (mean) APTC PTC temp [w = wt], by(flpdyr xtile) fast
rename temp exch_income
reshape wide APTC PTC exch_income, i(xtile) j(flpdyr)

gen agi_bin = ""
local i = 0
foreach bin in "<10" "10-20" "20-30" "30-40" "40-50" "50-60" "60-70" "70+" {
	replace agi_bin = "`bin'" if xtile == `i'
	local i = `i' + 1
}
replace agi_bin = "70+" if xtile > 6

local day = day(date(c(current_date), "DMY"))
if(`day' < 10) local day = ".0" + "`day'"
else local day = "." + "`day'"
local month = month(date(c(current_date), "DMY"))
if(`month' < 10) local month = ".0"+"`month'"
else local month = "." + "`month'"
local year = year(date(c(current_date), "DMY"))

order agi_bin PTC2021 APTC2021 exch_income2021
sort xtile
keep PTC2021 APTC2021 exch_income2021

mkmat PTC2021 APTC2021 exch_income2021, matrix(output)
putexcel set "$workingDir/output/Figure_1/Figure_1_PTC_by_HHAGI.xlsx", sheet("V4_data_21_ext") modify
putexcel B1 = matrix(output), colnames
putexcel close

restore
